A computer database is a structured collection of records or data stored in a computer system. The term “database” refers to the collection of interrelated information records and software components designed to maintain and provide access to the information, in ways including maintaining, adding, deleting, updating, querying, retrieving, generating reports and other operations. Databases are generally manipulated using query language commands, such as SQL, optionally comprising sub-categories such as Data Manipulation Language (DML), Data Control Language (DCL), and Data Control Language (DCL), or any proprietary command set used for High Availability (HA), Disaster Recovery (DR), or the like.
A database can be arranged in a number of ways, the most common being relational databases in which the information is organized in relations, often referred to as tables; hierarchical databases in which the information is organized as one or more object hierarchies in which objects have ancestor and descendent relationship; network databases, in which the information is organized as a lattice, in which each object is connected to multiple other objects in ancestor/descendent relationship, and Object Oriented Data Bases (OODB) in which information is represented in the form of objects as used in Object-Oriented Programming information.
A data base is typically constructed upon a structural description of the information, referred to as a schema. The schema contains the database objects that reflect the entities and their interrelations of the universe of disclosure that the database represents.
The performance of a database or a computerized environment comprising a database can be evaluated using multiple parameters, including time-related parameters such as response time or query duration, storage requirements, concurrency, integrity, and recovery from failures.
The time-related performance of a database, i.e. the time required for various actions, and the storage requirements depend upon many factors. The factors include but not limited to: the type of database; the specific arrangement of the database; the type and frequency of actions performed, e.g. more data modifications vs. more data retrievals; the hardware equipment used in the system; communication channels and their characteristics; specific implementations of objects such as stored procedures; query coding syntax; coding techniques; database and environment variables and configuration settings and additional factors.
There are a variety of commercial tools for testing one or more aspects of database performance, the tools implemented either as a part of a database or as an external unit. However, no such tool is known that tests multiple possible causes of performance deficiencies, including the database scheme, hardware, usage modes and others. Further, the possible causes are not independent of each other. Rather, they may strongly influence each other, such that correcting one problem worsens another. For example, if a table is often queried for data retrieval, adding another index may speed up the performance of data retrieval queries. However, if the same table is also frequently modified, the additional index may harm the modification performance. In another example, improving the hardware may significantly speed up the performance of hardware-related issues in, but may have negligible contribution to the overall performance, and is thus not worth the investment. Thus, tools that test or provide recommendations as to improving database performance, do not refer to interrelations between different factors, and their recommendations are therefore superficial and do not get to the root of the problems.
In addition, such tools may provide numerous results, which may or may not be prioritized. However, the interrelationships between the various factors can make locating the best cost effective factors impossible without deep understanding of databases in general and the specific database in particular.
Yet another problem relates to correcting the detected problems and their causes. A user may not have fill understanding of the database so that he or she can fix the problem. Often, an approach of not fixing a working system is applied, which prevents improvements and enhancement, because of the fear to worsen performance or break application dependencies which may cause a more serious issue than the original one.
There is thus a need for a method and apparatus that will enable automatic detection or correction of the root causes of database performance issues, and bottlenecks in databases. The method and apparatus should identify the causes of performance problems and suggest ways of enhancing and improving them. The method and apparatus should also be able to fix some of the problems without requiring user expertise.